package com.fzfnn.scrapbook.mapper;

import com.fzfnn.scrapbook.entity.Journal;
import com.fzfnn.scrapbook.vo.JournalVo;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface JournalMapper {
    @Insert("insert into journal (user_id, title, content, created_at,updated_at,is_public,img_url,tag) values (#{userId}, #{title}, #{content}, #{createdAt},#{updatedAt},#{isPublic},#{imgurl},#{tag})")
    int insertJournal(Journal journal);


    @Select("SELECT journal_id AS journalId,title,content, user_id AS userId, updated_at AS updatedAt, created_at AS createdAt, img_url AS imgurl, is_public AS isPublic,like_count AS likeCount,tag FROM journal where is_public =1 ORDER BY RAND()")
    List<Journal> getJournals();

    @Select("SELECT " +
        "j.journal_id AS journalId, " +
        "j.title, j.content, " +
        "j.user_id AS userId, " +
        "j.updated_at AS updatedAt, " +
        "j.created_at AS createdAt, " +
        "j.img_url AS imgurl, " +
        "j.like_count AS likeCount, " + "j.tag,"+
        "u.username, u.imgurl AS avatarUrl, u.user_id AS userId " +
        "FROM journal j " +
        "JOIN user u ON j.user_id = u.user_id " +
        "WHERE j.is_public = 1 ORDER BY RAND() ")

    List<JournalVo> getJournalsbyPublic();


    int updateJournal(
                @Param("title") String title,
                @Param("content") String content,
                @Param("updatedAt") Timestamp updatedAt,
                @Param("imgurl") String imgurl,
                @Param("journalId") int journalId,
                @Param("userId") int userId,
                @Param("tag") String tag
    );
    @Select("SELECT j.journal_id AS journalId, j.title, j.content, j.user_id AS userId, " +
            "j.updated_at AS updatedAt, j.created_at AS createdAt, j.img_url AS imgurl, j.tag," +
            "j.like_count AS likeCount, u.username AS userName, u.imgurl AS userAvatar " +
            "FROM journal j " +
            "JOIN user u ON j.user_id = u.user_id " +
            "WHERE j.journal_id = #{journalId} AND j.is_public = 1")
    Journal getJournalByIdWithUserInfo(@Param("journalId") int journalId);


    @Delete("DELETE FROM journal WHERE journal_id = #{journalId} AND user_id = #{userId}")
    int deleteJournal(@Param("journalId") int journalId, @Param("userId") int userId);

    @Select("SELECT journal_id AS journalId,title,content, user_id AS userId, updated_at AS updatedAt, created_at AS createdAt, img_url AS imgurl, is_public AS isPublic,like_count AS likeCount,tag FROM journal WHERE is_public = 1 ORDER BY RAND() DESC LIMIT #{size} OFFSET #{offset}")
    List<Journal> selectPublicJournalsByPage(@Param("offset") int offset, @Param("size") int size);

    @Select("SELECT COUNT(*) FROM journal WHERE is_public = 1")
    int countPublicJournals();

    @Select("<script>" +
            "SELECT journal_id AS journalId,title,content, user_id AS userId, updated_at AS updatedAt, created_at AS createdAt, img_url AS imgurl, is_public AS isPublic,like_count AS likeCount,tag FROM journal " +
            "WHERE is_public = 1 AND user_id IN " +
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>#{id}</foreach>" +
            "ORDER BY created_at DESC" +
            "</script>")
    List<Journal> getJournalsFromFollowed(@Param("ids") List<Integer> ids);


    @Update("UPDATE journal SET is_public = IF(is_public = 1, 0, 1) " +
            "WHERE journal_id = #{journalId} AND user_id = #{userId}")
    int toggleVisibility(@Param("journalId") Integer journalId, @Param("userId") Integer userId);

    @Select("SELECT is_public FROM journal WHERE journal_id = #{journalId}")
    int getVisibility(@Param("journalId") Integer journalId);
    @Select("SELECT " +
            "j.journal_id AS journalId, " +
            "j.title, j.content, " +
            "j.user_id AS userId, " +
            "j.updated_at AS updatedAt, " +
            "j.created_at AS createdAt, " +
            "j.img_url AS imgurl, " +
            "j.tag,"+
            "j.like_count AS likeCount, " +
            "u.username, u.imgurl AS avatarUrl, u.user_id AS userId " +
            "FROM journal j " +
            "JOIN user u ON j.user_id = u.user_id " +
            "WHERE j.user_id =#{userId}")
    List<JournalVo> getJournalsWithUserIdbymyself(@Param("userId")int userId);
//    @Select("SELECT " +
//            "j.journal_id AS journalId, " +
//            "j.title, j.content, " +
//            "j.user_id AS userId, " +
//            "j.updated_at AS updatedAt, " +
//            "j.created_at AS createdAt, " +
//            "j.img_url AS imgurl, " +
//            "j.like_count AS likeCount, " +
//            "u.username, u.imgurl AS avatarUrl, u.user_id AS userId, " +
//            "j.tag,"+
//            "FROM journal j " +
//            "JOIN user u ON j.user_id = u.user_id " +
//            "WHERE j.is_public = 1 and j.user_id =#{userId}")

    @Select("SELECT " +
            "j.journal_id AS journalId, " +
            "j.title, " +
            "j.content, " +
            "j.user_id AS userId, " +
            "j.updated_at AS updatedAt, " +
            "j.created_at AS createdAt, " +
            "j.img_url AS imgurl, " +
            "j.like_count AS likeCount, " +
            "u.username, " +
            "u.imgurl AS avatarUrl, " +
            "u.user_id AS userId, " +
            "j.tag " +
            "FROM journal j " +
            "JOIN user u ON j.user_id = u.user_id " +
            "WHERE j.is_public = 1 AND j.user_id = #{userId}")
    List<JournalVo> getJournalsWithUserId(@Param("userId")int userId);

    // 查询用户发布、点赞、收藏过的所有手账对应的tag（字符串数组，逗号分隔）
    @Select({
            "<script>",
            "SELECT DISTINCT tag FROM journal WHERE journal_id IN (",
            " SELECT journal_id FROM journal WHERE user_id = #{userId}",
            " UNION ",
            " SELECT journal_id FROM `journal_like` WHERE user_id = #{userId}",
            " UNION ",
            " SELECT journal_id FROM journal_collection WHERE user_id = #{userId}",
            ")",
            "AND tag IS NOT NULL AND tag != ''",
            "</script>"
    })
    List<String> selectTagsByUserActivity(@Param("userId") Integer userId);

    // 个性化推荐分页查询，根据用户tag列表匹配journal.tag
    @Select({
            "<script>",
            "SELECT",
            "  j.journal_id AS journalId,",
            "  j.title,",
            "  j.content,",
            "  j.user_id AS userId,",
            "  u.username AS userName,",
            "  u.imgurl AS userAvatar,",
            "  j.updated_at AS updatedAt,",
            "  j.created_at AS createdAt,",
            "  j.img_url AS imgurl,",
            "  j.is_public AS isPublic,",
            "  j.like_count AS likeCount,",
            "  j.tag",
            "FROM journal j",
            "LEFT JOIN user u ON j.user_id = u.user_id",
            "WHERE j.is_public = 1",
            "<if test='tagList != null and tagList.size() > 0'>",
            "AND (",
            "<foreach collection='tagList' item='tag' separator=' OR '>",
            "j.tag LIKE CONCAT('%', #{tag}, '%')",
            "</foreach>",
            ")",
            "</if>",
            "ORDER BY RAND()",
            "LIMIT #{size} OFFSET #{offset}",
            "</script>"
    })
    List<Journal> selectRecommendedPublicJournals(@Param("tagList") List<String> tagList,
                                                  @Param("size") int size,
                                                  @Param("offset") int offset);

    // 统计个性化推荐总数
    @Select({
            "<script>",
            "SELECT COUNT(*) FROM journal WHERE is_public = 1",
            "<if test='tagList != null and tagList.size() > 0'>",
            "AND (",
            "<foreach collection='tagList' item='tag' separator=' OR '>",
            "tag LIKE CONCAT('%', #{tag}, '%')",
            "</foreach>",
            ")",
            "</if>",
            "</script>"
    })
    int countRecommendedPublicJournals(@Param("tagList") List<String> tagList);


}
